import requests
import csv
import time
from lxml import etree
import pprint
import pymysql
import re

class ChinaBook:
    def __init__(self):
        self.header={
              'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/99.0.4844.82 Safari/537.36'
                }
        self.url='http://www.bookschina.com/book_find2/default.aspx?stp={}&scate=0&f=1&sort=0&asc=0&sh=0&so=1&p={}&pb=1'
        self.all_html='//*[@id="container"]/div/div[1]/div[1]/ul/li'
        self.xq_html='//div[@class="infor"]/h2/a/@href'
        self.title_html='//div[@class="infor"]/h2/a/@title'
        self.author_html='//div[@class="infor"]/div[1]/a[1]/text()'
        self.time_html='//div[@class="infor"]/div[1]/span/text()'
        self.publice_html='//div[@class="infor"]/div[1]/a[2]/text()'
        self.money_html='//div[@class="priceWrap"]/span[1]/text()'
        self.introduct_html='//p[@class="recoLagu"]'

        self.sql="""insert into libraey_book(book_name,author,time_date,money,introduct,public,link) values ("{0}","{1}","{2}","{3}","{4}","{5}","{6}")"""
        self.table_name='libraey_book'
    #主函数----查询的关键词以及想要获取到的页数
    def main(self,keywork,page):
        for i in range(0,page):
            url=self.url.format(keywork,i+1)
            response = requests.get(url, headers=self.header)
            html_text = etree.HTML(response.text)
            # print(response.text)
            mql_html = html_text.xpath(self.all_html)[0]
            xq_html = mql_html.xpath(self.xq_html)
            title = mql_html.xpath(self.title_html)
            author = mql_html.xpath(self.author_html)
            time = mql_html.xpath(self.time_html)
            publice = mql_html.xpath(self.publice_html)
            money = mql_html.xpath(self.money_html)
            introduct = mql_html.xpath(self.introduct_html)
            introduct_list = []
            for i in range(len(introduct)):
                a = introduct[i].xpath('text()')
                if a == 'None':
                    introduct_list.append('无')
                else:
                    introduct_list.append(a)
            #将数据插入数据库当中
            #先创建数据表
            self.create_table()
            #向表中插入数据
            for i in range(len(xq_html)):
                sql1 =self.sql.format(title[i], author[i], time[i].split('/')[0].strip(), money[i][1:], introduct_list[i],
                                  publice[i], xq_html[i])
                # 执行sql指令
                # print(sql1)
                try:
                    # 检查连接是否存在，断开的话会重
                    self.db.ping(reconnect=True)
                    self.con.execute(sql1)
                    self.db.commit()  # 确认交易
                    print("新增一笔记录")
                except:
                    self.db.rollback()
                    print('新增记录失败')
                self.db.close()


    #创建表
    def create_table(self):
        # #创建数据库连接
        self.db = pymysql.connect("localhost", "root", '123456', "mql", charset='utf8')
        # 创建cursor对象
        self.con = self.db.cursor()

        # 判断表的存在
        sql = "show tables;"
        self.con.execute(sql)
        tables = [self.con.fetchall()]
        table_list = re.findall('(\'.*?\')', str(tables))
        table_list = [re.sub("'", '', each) for each in table_list]
        if self.table_name in table_list:
            # 存在返回1
            return 1
        else:
            # 不存在返回
            sqll = """create table libraey_book
                     (book_name char(200) primary key  not null,
                       author text not null,
                       time_date date,
                       money real,
                       introduct char(255),
                       public char(20),
                       link char(50));"""
            self.con.execute(sqll)
            self.db.commit()
            print("创建成功")

if __name__=='__main__':
    mql=ChinaBook()
    mql.main('python',2)




